#!usr/bin/env python
# -*- coding: utf-8 -*-
'''
用于上市公司信息的数据清洗
'''

import sys
import re
import functools
import traceback
from copy import deepcopy
from datetime import datetime
from xtls.logger import get_logger
from company_crawler_util import camel_to_underline
from company_crawler_util import InsertError
from bs4 import BeautifulSoup

reload(sys)
sys.setdefaultencoding('utf-8')


def clean_str_null(data_dict):
    for key in data_dict.keys():
        if data_dict.get(key) == 'null':
            data_dict[key] = ''
    return data_dict


def get_id_from_mapping_db(conn, table, value, display=u''):
    """
    从table中获取name=value的id,如果table中没有name=value,则在该表中新增一条记录,默认name==display_name
    :param conn: 数据库的连接
    :param table: 表名
    :param value: 查询的name
    :return: name=value对应的id
    """
    logger = get_logger(__file__)
    if not value:
        return None
    sql = 'SELECT id FROM %s WHERE name=%s' % (table, '%s')
    cur = conn.cursor()
    try:
        cur.execute(sql, (value,))
        _id = cur.fetchone()
        if not _id:
            if not display:
                display = value
            sql = 'INSERT INTO %s (name, display_name) VALUES (%s, %s) RETURNING id' % (table, '%s', '%s')
            cur.execute(sql, (value, display))
            _id = cur.fetchone()
            conn.commit()
            _id = _id[0] if _id else None
        else:
            _id = _id[0]
    except Exception:
        trace = traceback.format_exc()
        error_statement = '%s \n InsertError in mapping table %s' % (trace, table)
        conn.commit()
        raise InsertError(error_statement)
    finally:
        cur.close()
    return _id

def deco_type_trans(func):
    logger = get_logger(__file__)
    @functools.wraps(func)
    def wrapped(*args, **kwargs):
        try:
            return func(*args, **kwargs)
        except ValueError:
            trace = traceback.format_exc()
            logger.exception(trace)
    return wrapped

class Cleaner(object):
    def __init__(self, data=None, logger=None, conn=None):
        self.data = data
        self.cleaned_data = {}
        self.logger = logger if logger else get_logger(__file__)
        self.re_element = re.compile(r'<[\w\W]*?>')
        # self.conn = conn

    def set_dirty_data(self, dirty_data):
        self.data = None
        self.data = self.init_clean(dirty_data)

    def init_clean(self, data):
        result = {}
        for key, value in data.items():
            value = self.clean_space(value)
            value = self.clean_odd_html(value)
            result[key] = value
        return result


    def clean_space(self, dirty_data):
        if isinstance(dirty_data, unicode) or isinstance(dirty_data, str):
            return dirty_data.strip() if dirty_data.strip() else None
        else:
            return dirty_data

    def clean_odd_html(self, dirty_data):
        if not dirty_data:
            return dirty_data
        if not '<' in dirty_data:
            return dirty_data
        value = self.re_element.sub('', dirty_data).strip()
        return value

    @deco_type_trans
    def _clean_ten_thousand_to_int(self, ten_thousand_str):
        if ',' in ten_thousand_str:
            ten_thousand_str = ten_thousand_str.replace(',', '')
        return int(float(ten_thousand_str) * 10000)

    @deco_type_trans
    def _clean_money_to_int(self, money_str):
        return int(money_str.replace(',', ''))

    @deco_type_trans
    def _clean_percent_to_float(self, input_str):
        return float(input_str) / 100

    @deco_type_trans
    def _clean_str_to_float(self, input_str):
        return float(input_str)

    def _clean_remove_http(self, website):
        website = website.replace('http://', '')
        website = website.replace('https://', '')
        return website

    @deco_type_trans
    def _clean_age_to_birth_year(self, age):
        age = int(age)
        current_year = datetime.today().year
        return '%d' % (current_year - age, )

    def _clean_get_id_single(self, value, table_name, conn):
        try:
            value_id = get_id_from_mapping_db(conn=conn, table=table_name, value=value)
        except InsertError:
            trace = traceback.format_exc()
            self.logger.exception(trace)
            value_id = None
        return value_id

    def _clean_get_id_list(self, value, table_name, conn):

        value = value.strip()
        for pattern in [',', u'，', '/', ';', u'；', '\\']:
            if pattern in value:
                value_list = value.split(pattern)
                value_list = [v.strip() for v in value_list]
                break
        else:
            value_list = [value]

        try:
            value_id_list = [get_id_from_mapping_db(conn=conn, table=table_name, value=value_single) for value_single in value_list]
        except InsertError:
            trace = traceback.format_exc()
            self.logger.exception(trace)
            value_id_list = None
        return value_id_list

    def _clean_inline_space(self, value):
        value = value.replace(u'\u3000', '')
        value = value.replace(u'\xa0', '')
        value = value.replace(' ', '')
        return value

    def _normalize_degree(self, value):
        value = self._clean_inline_space(value)
        normal_mapping = [{'normal_value': u'博士', 'contain_value': [u'博士', u'院士', u'博导']},
                          {'normal_value': u'硕士', 'contain_value': [u'硕士', u'研究生', u'硕']},
                          {'normal_value': u'MBA及EMBA', 'contain_value': [u'mba', u'emba']},
                          {'normal_value': u'大专', 'contain_value': [u'大专', u'专科', u'高职', u'成人', u'函授']},
                          {'normal_value': u'本科', 'contain_value': [u'本科', u'学士', u'双本', u'大本', u'大学']},
                          {'normal_value': u'高中及以下', 'contain_value': [u'高中', u'初中', u'中学', u'小学', u'中专', u'职高', u'中技']},
                          {'normal_value': value, 'contain_value': [value]},
                          ]

        for mapping_dict in normal_mapping:
            for contain_value in mapping_dict.get('contain_value', list()):
                if contain_value in value.lower():
                    return mapping_dict.get('normal_value')

    def _clean_get_degree_id(self, value, table_name, conn):
        try:
            value = self._normalize_degree(value)
            value_id = get_id_from_mapping_db(conn=conn, table=table_name, value=value)
        except InsertError:
            trace = traceback.format_exc()
            self.logger.exception(trace)
            value_id = None
        return value_id

    def clean_type_project(self, key_list, clean_plan):
        for key in key_list:
            value = self.data.pop(key, None)
            if not value:  # value=None or value=''
                self.cleaned_data[key] = None
                continue
            value = clean_plan(value)
            self.cleaned_data[key] = value

    def clean_get_id_project(self, key_list, clean_plan, conn):
        for key in key_list:
            value = self.data.pop(key, None)
            if not value:
                self.cleaned_data['%sId' % key] = None
                continue
            table_name = 'company_mapping_%s' % camel_to_underline(key)  # 去mapping表中的id
            value_id = clean_plan(conn=conn, table_name=table_name, value=value)
            self.cleaned_data['%sId' % key] = value_id

    def clean_ten_thousand_to_int(self, key_list):
        if not isinstance(key_list, list):
            key_list = [key_list]
        self.clean_type_project(key_list=key_list, clean_plan=self._clean_ten_thousand_to_int)

    def clean_money_to_int(self, key_list):
        if not isinstance(key_list, list):
            key_list = [key_list]
        self.clean_type_project(key_list=key_list, clean_plan=self._clean_money_to_int)

    def clean_percent_to_float(self, key_list):
        if not isinstance(key_list, list):
            key_list = [key_list]
        self.clean_type_project(key_list=key_list, clean_plan=self._clean_percent_to_float)

    def clean_str_to_float(self, key_list):
        if not isinstance(key_list, list):
            key_list = [key_list]
        self.clean_type_project(key_list=key_list, clean_plan=self._clean_str_to_float)

    def clean_remove_http(self, key_list):
        if not isinstance(key_list, list):
            key_list = [key_list]
        self.clean_type_project(key_list=key_list, clean_plan=self._clean_remove_http)

    def clean_age_to_brith_year(self, key_list):
        if not isinstance(key_list, list):
            key_list = [key_list]
        self.clean_type_project(key_list=key_list, clean_plan=self._clean_age_to_birth_year)

    def clean_get_degree_id(self, key_list, conn):
        if not isinstance(key_list, list):
            key_list = [key_list]
        self.clean_get_id_project(key_list=key_list, clean_plan=self._clean_get_degree_id, conn=conn)

    def clean_get_id_single(self, key_list, conn):
        if not isinstance(key_list, list):
            key_list = [key_list]
        self.clean_get_id_project(key_list=key_list, clean_plan=self._clean_get_id_single, conn=conn)

    def clean_get_id_list(self, key_list, conn):
        if not isinstance(key_list, list):
            key_list = [key_list]
        self.clean_get_id_project(key_list=key_list, clean_plan=self._clean_get_id_list, conn=conn)

    def complete(self):
        for key, value in self.data.items():
            # value = self.clean_space(value)
            # value = self.clean_odd_html(value)
            self.cleaned_data[key] = value
        cleaned_data = self.cleaned_data
        self.cleaned_data = {}
        self.data = {}
        return cleaned_data

if __name__ == '__main__':
    # an example as follows
    single = {
        'mon2int': '12123,123,123,123',
        'wan2ge1': '1123,1231.12',
        'wan2ge2': '123112.012',
        'perc2flo': '123.11',
        'dirty_str': '   ',
        'date': ' 2015-12-11',
        'date2': '20150104',
        'common_str': 'adsf ',
        'str2float': '#####',
        'website1': 'http://ashdkajsdkj',
        'website2': 'https://ashdkajsdkj',
        'age': '43',
        "name": """ <html> <head> <meta http-equiv=Content-Type content="text/html; charset=gb2312"> <meta name=Generator content="Microsoft Word 15 (filtered)"> <style> <!-- /* Font Definitions */ @f
ont-face {font-family:宋体; panose-1:2 1 6 0 3 1 1 1 1 1;} @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;} @font-face {font-family:"\@宋体"; panose-1:2 1 6 0 3 1 1 1 1 1;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; font-size:10.5pt; font-family:"Calibri",sans-serif;} /* Page Definitions */ @page WordSection1 {size:595.3pt 841.9pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; layout-grid:15.6pt;} div.WordSection1 {page:WordSection1;} --> </style> </head> <body lang=ZH-CN style='text-justify-trim:punctuation'> <div class=WordSection1 style='layout-grid:15.6pt'> <p class=MsoNormal><span style='font-size:10.0pt;font-family:宋体'>硕士</span></p> </div> </body> </html>
""",
        "gender": "<html> <head> <meta http-equiv=Content-Type content=\"text/html; charset=gb2312\"> <meta name=Generator content=\"Microsoft Word 15 (filtered)\"> <style> <!-- /* Font Definitions */ @font-face {font-family:\u5b8b\u4f53; panose-1:2 1 6 0 3 1 1 1 1 1;} @font-face {font-family:\"Cambria Math\"; panose-1:2 4 5 3 5 4 6 3 2 4;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;} @font-face {font-family:\"\\@\u5b8b\u4f53\"; panose-1:2 1 6 0 3 1 1 1 1 1;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; font-size:10.5pt; font-family:\"Calibri\",sans-serif;} /* Page Definitions */ @page WordSection1 {size:595.3pt 841.9pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; layout-grid:15.6pt;} div.WordSection1 {page:WordSection1;} --> </style> </head> <body lang=ZH-CN style='text-justify-trim:punctuation'> <div class=WordSection1 style='layout-grid:15.6pt'> <p class=MsoNormal><span style='font-size:10.0pt;font-family:\u5b8b\u4f53'>\u7537</span></p> </div> </body> </html>",
        "job": "<html> <head> <meta http-equiv=Content-Type content=\"text/html; charset=gb2312\"> <meta name=Generator content=\"Microsoft Word 15 (filtered)\"> <style> <!-- /* Font Definitions */ @font-face {font-family:\u5b8b\u4f53; panose-1:2 1 6 0 3 1 1 1 1 1;} @font-face {font-family:\"Cambria Math\"; panose-1:2 4 5 3 5 4 6 3 2 4;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;} @font-face {font-family:\"\\@\u5b8b\u4f53\"; panose-1:2 1 6 0 3 1 1 1 1 1;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; font-size:10.5pt; font-family:\"Calibri\",sans-serif;} /* Page Definitions */ @page WordSection1 {size:595.3pt 841.9pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; layout-grid:15.6pt;} div.WordSection1 {page:WordSection1;} --> </style> </head> <body lang=ZH-CN style='text-justify-trim:punctuation'> <div class=WordSection1 style='layout-grid:15.6pt'> <p class=MsoNormal><span style='font-size:10.0pt;font-family:\u5b8b\u4f53'>\u8463\u4e8b</span></p> </div> </body> </html>",
        "education": "<html> <head> <meta http-equiv=Content-Type content=\"text/html; charset=gb2312\"> <meta name=Generator content=\"Microsoft Word 15 (filtered)\"> <style> <!-- /* Font Definitions */ @font-face {font-family:\u5b8b\u4f53; panose-1:2 1 6 0 3 1 1 1 1 1;} @font-face {font-family:\"Cambria Math\"; panose-1:2 4 5 3 5 4 6 3 2 4;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;} @font-face {font-family:\"\\@\u5b8b\u4f53\"; panose-1:2 1 6 0 3 1 1 1 1 1;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0cm; margin-bottom:.0001pt; text-align:justify; text-justify:inter-ideograph; font-size:10.5pt; font-family:\"Calibri\",sans-serif;} /* Page Definitions */ @page WordSection1 {size:595.3pt 841.9pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; layout-grid:15.6pt;} div.WordSection1 {page:WordSection1;} --> </style> </head> <body lang=ZH-CN style='text-justify-trim:punctuation'> <div class=WordSection1 style='layout-grid:15.6pt'> <p class=MsoNormal><span style='font-size:10.0pt;font-family:\u5b8b\u4f53'>\u5b66\u58eb</span></p> </div> </body> </html>"
    }

    # instance
    clean_handler = Cleaner()
    clean_handler.set_dirty_data(single)
    clean_handler.clean_money_to_int('mon2int')
    clean_handler.clean_ten_thousand_to_int(['wan2ge2', 'wan2ge1'])
    clean_handler.clean_percent_to_float(['perc2flo'])
    clean_handler.clean_str_to_float(['str2float'])
    clean_handler.clean_remove_http(['website1', 'website2'])
    clean_handler.clean_age_to_brith_year(['age'])

    single_cleaned = clean_handler.complete()

    for key, value in single_cleaned.items():
        print '{}: {}'.format(key, value)

    print clean_handler._clean_inline_space(u'专 科')